package database;

import database.common.ConnectionFactory;
import model.Artikull;
import model.app.Department;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   3/10/13
 * Time:   10:33 PM
 */
public class ArtikullDao {

    private static final String INSERT_ARTIKULL =
            "INSERT INTO public.artikull ( kodi, emertimi, pershkrimi, cmimi_blerjes, sasia_min_stok, sasia_min_furnizim,status,kategoria_id)" +
                    " VALUES (?,?,?,?,?,?,?,?)";
    private static final String UPDATE_ARTIKULL =
            "UPDATE public.artikull SET kodi=?, emertimi=?, pershkrimi=?, cmimi_blerjes=?, sasia_min_stok=?, sasia_min_furnizim=?,status=?,kategoria_id=? WHERE id=? ";
    private static final String DELETE_ARTIKULL =
            "DELETE FROM public.artikull WHERE id = ?";
    private static final String SELECT_SINGLE_ARTIKULL =
            "SELECT id, kodi, emertimi, pershkrimi, cmimi_blerjes, sasia_min_stok, sasia_min_furnizim,status,kategoria_id FROM public.artikull WHERE id=?";
    private static final String SELECT_ALL_ARTIKUJ =
            "SELECT * from query_to_xml('SELECT * FROM public.artikull',true,false,'')";
    private static final String SELECT_ARTIKUJ_KATEGORI =
            "SELECT id, kodi, emertimi, pershkrimi, cmimi_blerjes, sasia_min_stok, sasia_min_furnizim,status,kategoria_id FROM public.artikull WHERE kategoria_id=?";


    public static int insert(Artikull artikull){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(INSERT_ARTIKULL);
            statement.setString(1, artikull.getKodi());
            statement.setString(2, artikull.getEmertimi());
            statement.setString(3, artikull.getPershkrimi());
            statement.setDouble(4, artikull.getCmimiBlerjes());
            statement.setDouble(5, artikull.getSasiaMinStock());
            statement.setDouble(6, artikull.getSasiaMinFurnizim());
            statement.setInt(7, artikull.getStatus());
            statement.setInt(8, artikull.getKategoriaID());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(Artikull artikull){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(UPDATE_ARTIKULL);
            statement.setString(1, artikull.getKodi());
            statement.setString(2, artikull.getEmertimi());
            statement.setString(3, artikull.getPershkrimi());
            statement.setDouble(4, artikull.getCmimiBlerjes());
            statement.setDouble(5, artikull.getSasiaMinStock());
            statement.setDouble(6, artikull.getSasiaMinFurnizim());
            statement.setInt(7, artikull.getStatus());
            statement.setInt(8, artikull.getKategoriaID());
            statement.setInt(9, artikull.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int delete(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(DELETE_ARTIKULL);
            statement.setInt(1, id);
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Artikull selectArtikull(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Artikull artikull = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_ARTIKULL);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                artikull = new Artikull();
                artikull.setId(resultSet.getInt(1));
                artikull.setKodi(resultSet.getString(2));
                artikull.setEmertimi(resultSet.getString(3));
                artikull.setPershkrimi(resultSet.getString(4));
                artikull.setCmimiBlerjes(resultSet.getDouble(5));
                artikull.setSasiaMinStock(resultSet.getDouble(6));
                artikull.setSasiaMinFurnizim(resultSet.getDouble(7));
                artikull.setStatus(resultSet.getInt(8));
                artikull.setKategoriaID(resultSet.getInt(9));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return artikull;
    }

    public static String selectAllArtikuj(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String result = "";
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_ALL_ARTIKUJ);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public static List<Artikull> selectArtikujByKategori(int kategoriaID){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Artikull> artikuj = new ArrayList<Artikull>();
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_ARTIKUJ_KATEGORI);
            statement.setInt(1, kategoriaID);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                Artikull artikull = new Artikull();
                artikull.setId(resultSet.getInt(1));
                artikull.setKodi(resultSet.getString(2));
                artikull.setEmertimi(resultSet.getString(3));
                artikull.setPershkrimi(resultSet.getString(4));
                artikull.setCmimiBlerjes(resultSet.getDouble(5));
                artikull.setSasiaMinStock(resultSet.getDouble(6));
                artikull.setSasiaMinFurnizim(resultSet.getDouble(7));
                artikull.setStatus(resultSet.getInt(8));
                artikull.setKategoriaID(resultSet.getInt(9));
                artikuj.add(artikull);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return artikuj;
    }

}
